{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'lxml'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-1-7f2b3ef1660e>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      8\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      9\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 10\u001b[1;33m \u001b[1;32mfrom\u001b[0m \u001b[0mlxml\u001b[0m \u001b[1;32mimport\u001b[0m \u001b[0metree\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     11\u001b[0m \u001b[1;32mimport\u001b[0m \u001b[0mrequests\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     12\u001b[0m \u001b[1;32mimport\u001b[0m \u001b[0mjson\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mre\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mModuleNotFoundError\u001b[0m: No module named 'lxml'"
     ]
    }
   ],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "\"\"\"\n",
    "Created on Wed Jun 17 16:17:38 2020\n",
    "\n",
    "@author: my\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "\n",
    "from lxml import etree\n",
    "import requests\n",
    "import json, re\n",
    "import MySQLdb\n",
    "\n",
    "\n",
    "url = 'https://h5api.m.taobao.com/h5/mtop.alimama.union.sem.landing.pc.items/1.0/?jsv=2.4.0&appKey=12574478&t=1592380690017&sign=368d4db4b977bab71716bb660e261167&api=mtop.alimama.union.sem.landing.pc.items&v=1.0&AntiCreep=true&dataType=jsonp&type=jsonp&ecode=0&callback=mtopjsonp1&data=%7B%22keyword%22%3A%22%E7%BA%A2%E5%AF%8C%E5%A3%AB%E8%8B%B9%E6%9E%9C%22%2C%22ppath%22%3A%22%22%2C%22loc%22%3A%22%22%2C%22minPrice%22%3A%22%22%2C%22maxPrice%22%3A%22%22%2C%22ismall%22%3A%22%22%2C%22ship%22%3A%22%22%2C%22itemAssurance%22%3A%22%22%2C%22exchange7%22%3A%22%22%2C%22custAssurance%22%3A%22%22%2C%22b%22%3A%22%22%2C%22clk1%22%3A%22df6530d16b3208828b55de25e9a6d643%22%2C%22pvoff%22%3A%22%22%2C%22pageSize%22%3A%22100%22%2C%22page%22%3A%220%22%2C%22elemtid%22%3A%221%22%2C%22refpid%22%3A%22mm_26632258_3504122_32538762%22%2C%22pid%22%3A%22430673_1006%22%2C%22featureNames%22%3A%22spGoldMedal%2CdsrDescribe%2CdsrDescribeGap%2CdsrService%2CdsrServiceGap%2CdsrDeliver%2C%20dsrDeliverGap%22%2C%22ac%22%3A%22zV0DFxG9PRUCASSAgdgexrXz%22%2C%22wangwangid%22%3A%22t_1514168928654_0773%22%2C%22catId%22%3A%22%22%7D'\n",
    "cookie=\"XSRF-TOKEN=c4493474-0afd-479d-9a58-d435314cac90;_m_h5_tk=220e8f757b61862e81dd3ce601a9db27_1592388012955;_m_h5_tk_enc=2c0e745962231609d8b8dc195968ce8d\"\n",
    "headers = {\n",
    "    'Accept': 'text/html, application/xhtml+xml, image/jxr, */*',\n",
    "               'Accept - Encoding':'gzip, deflate',\n",
    "               'Accept-Language':'zh-Hans-CN, zh-Hans; q=0.5',\n",
    "               'Connection':'Keep-Alive',\n",
    "    'Referer': 'https://uland.taobao.com/sem/tbsearch?refpid=mm_26632258_3504122_32538762&clk1=df6530d16b3208828b55de25e9a6d643&keyword=%E7%BA%A2%E5%AF%8C%E5%A3%AB%E8%8B%B9%E6%9E%9C&page=0',\n",
    "    'User-Agent':'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.90 Safari/537.36',\n",
    "    'Cookie': cookie\n",
    "    \n",
    "    }\n",
    "\n",
    "response = requests.get(url, headers=headers )\n",
    "response.encoding = 'utf-8'\n",
    "\n",
    "print(response.text)\n",
    "\n",
    "data = json.loads(re.match(\".*?({.*}).*\", response.text, re.S).group(1))\n",
    "\n",
    "print(data)\n",
    "\n",
    "\n",
    "\n",
    "# 打开数据库连接\n",
    "db = MySQLdb.connect(\"localhost\", \"root\", \"123456\", \"lexian\", port=3306,charset='utf8' )\n",
    "\n",
    "# 使用cursor()方法获取操作游标 \n",
    "cursor = db.cursor()\n",
    "\n",
    "# 使用execute方法执行SQL语句\n",
    "sql = \"\"\"INSERT INTO  taobao (title, loc, price, DATA) VALUES(%s, %s, %f, CURRENT_DATE ) \"\"\" \n",
    "print(sql)\n",
    "try:\n",
    "    \n",
    "    for item in data:\n",
    "            \n",
    "        #INSERT INTO  taobao (title, loc, price, DATA) VALUES(%s, %s, %f, CURRENT_DATE )\n",
    "        cursor.execute(sql, (item.title, item.loc, float(item.price)))\n",
    "\n",
    "    db.commit()\n",
    "except Exception as e:\n",
    "    db.rollback()\n",
    "    print(e)\n",
    "    \n",
    "\n",
    "# 关闭数据库连接\n",
    "db.close()\n",
    "\n",
    "    \n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
